== SQL Database plugin

`DbPlugin` is a wrapper around http://dbunit.sourceforge.net[DbUnit] library that enables setting up and verification of database.

.Exam integration with database
[plantuml, db]
--
allowmixing
left to right direction

component DbPlugin #AFF {
    class DbTester
    component DbUnit
}

file Datasets #d1e7dd
database DB #d1e7dd

DbTester --> DbUnit :configures
DbUnit --> Datasets :uses
DbUnit --> DB :interacts with
--

.Dependency
[source,groovy,subs="attributes+"]
testImplementation "io.github.adven27:exam-db:{version}"

.Configuration
[source,kotlin]
--
class Specs : AbstractSpecs() {
    override fun init() = ExamExtension(
        DbPlugin(
            driver = "org.postgresql.Driver",
            url = "jdbc:postgresql://localhost:5432/postgres",
            user = "postgres",
            password = "postgres"
        )
    )
--

.Commands
[unstyled]
- `<<e-db-set>>`
- `<<e-db-check>>`
- `<<e-db-clean>>`
- `<<e-db-execute>>`
- `<<e-db-verify>>`
- `<<e-db-show>>`

=== e-db-set [[e-db-set]]

Creates http://dbunit.sourceforge.net/[DbUnit] dataset for specified table and applies it to database.

.Attributes
[horizontal]
include::attr-operation.adoc[]
include::attr-ds.adoc[]

.Usage
[source,asciidoc]
--
include::plugin-db.adoc[tags=db-set]
--

====
//tag::db-set[]
.Set up table `PRODUCT`
[%header,format=csv, e-db-set=product]
|===
include::../data/db/product.csv[]
|===
//end::db-set[]
====

=== e-db-check [[e-db-check]]

Creates http://dbunit.sourceforge.net/[DbUnit] dataset for table and verifies it against a database.

.Attributes
[horizontal]
include::attr-where.adoc[]
include::attr-orderBy.adoc[]
include::attr-ds.adoc[]
include::attr-await.adoc[]

.Usage
[source,asciidoc]
--
include::plugin-db.adoc[tags=db-check]
--

====
//tag::db-check[]
.Check table `PRODUCT`
[%header,format=csv, e-db-check=product]
|===
include::../data/db/product.csv[]
|===
//end::db-check[]
====

.Check with matchers
[source,asciidoc]
--
include::plugin-db.adoc[tags=db-check-matcher]
--

====
//tag::db-check-matcher[]
.Check with matchers
[e-db-check=product]
|===
|name |price |rating |disabled |created_at |meta_json

|{{string}}
|{{number}}>>priceA
|{{notNull}}
|{{bool}}>>disabledA
|{{within '15s' (at '-1d')}}
|{"id": "{{string}}", "code": "{{number}}"}

|{{regex '.*'}}>>nameB
|{{ignore}}
|{{ignore}}>>ratingB
|true
|{{within '1m'}}>>createdB
|{"id": "{{regex '\\w'}}", "code": "{{ignore}}"}
|===

nameB = [e-eq=#nameB]`Model B`
priceA = [e-eq=#priceA]`100.50`
ratingB = [e-eq=#ratingB]`(null)`
disabledA = [e-eq=#disabledA]`false`
createdB = [e-eq=#createdB]`{{format (at) 'yyyy-MM-dd HH:mm:ss.SSS'}}`
//end::db-check-matcher[]
====

.Check ordered
[source,asciidoc]
--
include::plugin-db.adoc[tags=db-check-ordered]
--

====
//tag::db-check-ordered[]
[e-db-check=product, e-orderBy='rating, id']
,===
name, rating

{{string}}, {{ignore}}
{{string}}, 10
,===
//end::db-check-ordered[]
====

.Check emptiness
[source,asciidoc]
--
include::plugin-db.adoc[tags=db-check-empty]
--

====
.Set empty table
[e-db-set=product]
|===
|===

//tag::db-check-empty[]
.Check table
[e-db-check=product]
|===
|===
//end::db-check-empty[]
====

=== e-db-clean [[e-db-clean]]

Cleans specified tables in a database.

.Attributes
[horizontal]
include::attr-ds.adoc[]

.Usage
[source,asciidoc]
--
include::plugin-db.adoc[tags=db-clean]
--

NOTE: Tables will be cleaned up in the order opposite to declaration, hence the "referenced" tables should go first and the "referencing" - last.

====
//tag::db-clean[]
Clean tables: [e-db-clean]#person, person_fields#
//end::db-clean[]
====

=== e-db-execute [[e-db-execute]]

Applies specified http://dbunit.sourceforge.net/[DbUnit] datasets to database.

.Attributes
[horizontal]
include::attr-operation.adoc[]
include::attr-ds.adoc[]
include::attr-dir.adoc[]

.Usage
[source,asciidoc]
--
include::plugin-db.adoc[tags=db-execute]
--

====
[.given]

.adam.xml
[source,xml]
--
include::../data/db/adam.xml[]
--

.bob.json
[source,json]
--
include::../data/db/bob.json[]
--

[e-set=a1 hide]#1#
[e-set=a2 hide]#2#

[.then]
//tag::db-execute[]
Execute datasets: +++<e e:db-execute='adam.xml, bob.json' dir='/data/db/'/>+++
//end::db-execute[]
====

=== e-db-verify [[e-db-verify]]

Verifies specified http://dbunit.sourceforge.net/[DbUnit] datasets against database.

.Attributes
[horizontal]
include::attr-dir.adoc[]
include::attr-orderBy.adoc[]
include::attr-ds.adoc[]
include::attr-await.adoc[]

.Usage
[source,asciidoc]
....
include::plugin-db.adoc[tags=db-verify]
....

====
[.given]

.adam.xml
[source,xml]
--
include::../data/db/adam.xml[]
--

.bob.json
[source,json]
--
include::../data/db/bob.json[]
--

[.then]
//tag::db-verify[]
Verify datasets: +++<e e:db-verify='adam.xml, bob.json' dir='/data/db/'/>+++
//end::db-verify[]
====

=== e-db-show [[e-db-show]]

Prints content of specified database table and optionally generates DbUnit dataset.

.Attributes
[horizontal]
include::attr-where.adoc[]
include::attr-ds.adoc[]
`saveTo`:: _Optional. Default: not set._
+
Path in 'srs/test/resources/' to store generated dataset

.Usage
[source,asciidoc]
--
include::plugin-db.adoc[tags=db-show]
--

====
//tag::db-show[]
.Show all and generate dataset
[e-db-show=person, e-saveTo=/data/db/person.xml]
,===
,===

.Show specific columns
[e-db-show=person]
,===
id, name

,===

.Show specific rows
[e-db-show=person, e-where="name='Bob'"]
,===
id, name

,===
//end::db-show[]
====

=== Details

- link:DbSetOperations.adoc[What are set operation options?, role=e-run]
- link:DbCheckFailures.adoc[How failures look like?, role=e-run]
- link:DbCheckContentTypes.adoc[How to check content types?, role=e-run]
